﻿using System;
using System.Data;
using DataAccess;
using DataAccess.i2iPortalTableAdapters;

namespace Business
{
	public class OilReportService
	{

		public DataTable getComponentsByMachinesAndStatusByDate(DataTable machines, DataTable Status, DateTime date)
		{
			CreateConnection cn = new CreateConnection();
			DataTable components = new DataTable();
			string query = "";
			string machineList = "";
			string statusList = "";
			bool first = true;
			foreach (DataRow r in machines.Rows)
			{
				string machine_id = r["machine_id"].ToString();
				if (first == true)
				{
					machineList = machineList + "(" + machine_id;
					first = false;
				}
				else
					machineList = machineList + "," + machine_id;
			}
			machineList = machineList + ")";
			first = true;
			foreach (DataRow r in Status.Rows)
			{
				string status_id = r["status_id"].ToString();
				if (first == true)
				{
					statusList = statusList + "(" + status_id;
					first = false;
				}
				else
				{
					statusList = statusList + "," + status_id;
				}
			}
			statusList = statusList + ")";

			query = query + " SELECT        o.reportlink, o.status, c.componentName, c.component_id, m.site_id" +
			        " FROM       OilReports AS o INNER JOIN" +
			        " Components AS c ON o.Component_id = c.component_id INNER JOIN" +
			        " Machines AS m ON c.machine_id = m.machine_id" +
			        " WHERE     (m.machine_id IN " + machineList + ") AND (o.status IN " + statusList +
			        ") AND (convert(date,o.tested_date,103) = CONVERT(date, '" + date + "', 103))";

			components = cn.CreateDataTable(query);

			return components;
		}

		public DataTable getSeverity()
		{
			CreateConnection cn = new CreateConnection();
			string severity = "select statusName,status_id,statusDescription from OilStatus;";
			DataTable dt = new DataTable();
			dt = cn.CreateDataTable(severity);
			return dt;
		}

		public DataTable getComponentsBySitesAndStatusByDate(DataTable sites, DataTable Status, string date)
		{
			CreateConnection cn = new CreateConnection();
			DataTable components = new DataTable();
			string query = "";
			string machineList = "";
			string statusList = "";
			bool first = true;
			foreach (DataRow r in sites.Rows)
			{
				string machine_id = r["site_id"].ToString();
				if (first == true)
				{
					machineList = machineList + "(" + machine_id;
					first = false;
				}
				else
					machineList = machineList + "," + machine_id;
			}
			machineList = machineList + ")";
			first = true;
			foreach (DataRow r in Status.Rows)
			{
				string status_id = r["status_id"].ToString();
				if (first == true)
				{
					statusList = statusList + "(" + status_id;
					first = false;
				}
				else
				{
					statusList = statusList + "," + status_id;
				}
			}
			statusList = statusList + ")";

			query = query + " SELECT        o.reportlink, o.status, c.componentName, c.component_id, m.site_id,m.machineName" +
			        " FROM       OilReports AS o INNER JOIN" +
			        " Components AS c ON o.Component_id = c.component_id INNER JOIN" +
			        " Machines AS m ON c.machine_id = m.machine_id" +
			        " WHERE     (m.site_id IN " + machineList + ") AND (o.status IN  (SELECT        statusName AS sn " +
			        "  FROM            OilStatus WHERE        (status_id IN  " +
			        statusList + "))) AND (convert(varchar(10),o.tested_date,3) = '" + date + "')";

			components = cn.CreateDataTable(query);

			return components;
		}

		public DataTable getOilReportByComponentId(string component_id)
		{
			CreateConnection cn = new CreateConnection();
			DataTable table = new DataTable();
			string query = "select sample_id,tested_date,receive_date,reportlink, status from Oilreports where component_id=" +
			               component_id + ";";
			table = cn.CreateDataTable(query);
			return table;
		}

		public DataTable getExceptionSeverity()
		{
			CreateConnection cn = new CreateConnection();
			string severity = "select statusName,status_id,statusDescription from OilStatus where (statusName <> 'Normal');";
			DataTable dt = new DataTable();
			dt = cn.CreateDataTable(severity);
			return dt;
		}

		public DataTable getLatestdate(int clientid)
		{
			CreateConnection cn = new CreateConnection();



			string severity = "SELECT        CONVERT(varchar(10), tested_date, 3) AS Expr1 " +
			                  "FROM            (SELECT        TOP (1) OilReports.tested_date " +
			                  "    FROM            Clients INNER JOIN " +
			                  "  Sites ON Clients.client_id = Sites.client_id INNER JOIN " +
			                  "  Machines ON Sites.site_id = Machines.site_id INNER JOIN " +
			                  "  Components ON Machines.machine_id = Components.machine_id INNER JOIN " +
			                  "  OilReports ON Components.component_id = OilReports.Component_id " +
			                  "   WHERE        (Clients.client_id = " + clientid + ")" +
			                  "  ORDER BY OilReports.tested_date DESC) AS q1";



			DataTable dt = new DataTable();
			dt = cn.CreateDataTable(severity);
			return dt;
		}

		public int GetOilStatusByMachineID(int machineId, DataTable sortedResult)
		{
			var adapterComp = new ComponentsTableAdapter();
			var components = adapterComp.GetDataByMachineID(machineId);
			var aptOil = new OilReportsTableAdapter();
			int status_ID = 0;
			foreach (var component in components)
			{
				var reportForCompt = aptOil.GetOilReportsByComponentID((Int32.Parse(component["component_id"].ToString())));
				if (reportForCompt.Rows.Count > 0)
				{
					foreach (DataRow reportRow in reportForCompt.Rows)
					{
						if (reportRow["status"] != DBNull.Value)
						{
							int temp = HelperClass.StatusToInt(reportRow["status"].ToString());
							if (temp > status_ID)
							{
								status_ID = temp;
							}
						}
						break;
					}
				}
			}
			return status_ID;
		}
	}
}
